/**
 * MySQL 数据库初始化脚本
 * 用于创建 tkshop 数据库、t_product 表和 t_product_videos 表
 */

const mysql = require('mysql2/promise');
const { createProductVideosTable } = require('./init-product-videos');
require('dotenv').config();

const DB_CONFIG = {
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || ''
};

const DB_NAME = process.env.DB_NAME || 'tkshop';

async function initializeDatabase() {
  let connection;

  try {
    console.log('连接到 MySQL 服务器...');
    connection = await mysql.createConnection(DB_CONFIG);

    console.log('MySQL 连接成功');

    // 创建数据库
    console.log(`创建数据库 ${DB_NAME}...`);
    await connection.query(`CREATE DATABASE IF NOT EXISTS \`${DB_NAME}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`);
    console.log(`数据库 ${DB_NAME} 创建成功`);

    // 关闭当前连接
    await connection.end();

    // 重新连接到目标数据库
    console.log(`连接到数据库 ${DB_NAME}...`);
    connection = await mysql.createConnection({
      ...DB_CONFIG,
      database: DB_NAME
    });

    // 创建 t_product 表
    await createProductTable(connection);

    // 创建 t_product_videos 表
    await createProductVideosTable(connection);

    // 插入示例数据
    await insertSampleData(connection);

    console.log('数据库初始化完成！');

  } catch (error) {
    console.error('数据库初始化失败:', error);

    // 提供更详细的错误信息
    if (error.code === 'ER_ACCESS_DENIED_ERROR') {
      console.log('💡 请检查数据库用户名和密码是否正确');
    } else if (error.code === 'ECONNREFUSED') {
      console.log('💡 请确保 MySQL 服务正在运行');
      console.log('   macOS: brew services start mysql');
      console.log('   Linux: sudo systemctl start mysql');
    } else if (error.code === 'ER_BAD_DB_ERROR') {
      console.log('💡 数据库连接配置可能有误');
    }

    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
      console.log('MySQL 连接已关闭');
    }
  }
}

/**
 * 创建商品表
 */
async function createProductTable(connection) {
  try {
    console.log('创建 t_product 表...');

    // 检查表是否已存在
    const [tables] = await connection.query(`
      SELECT TABLE_NAME 
      FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 't_product'
    `, [DB_NAME]);

    if (tables.length > 0) {
      console.log('t_product 表已存在，跳过创建');
      return;
    }

    const createTableSQL = `
      CREATE TABLE t_product (
        id VARCHAR(50) NOT NULL PRIMARY KEY COMMENT '商品ID',
        productName VARCHAR(1000) NOT NULL COMMENT '商品名称',
        \`rank\` INT NULL COMMENT '排名',
        priceUsd DECIMAL(10,2) NULL COMMENT '价格（USD）',
        price DECIMAL(10,2) NULL COMMENT '价格（RMB）',
        sellerName VARCHAR(500) NULL COMMENT '店铺名称',
        weekSoldCount INT NULL COMMENT '周销售量',
        weekSales DECIMAL(15,2) NULL COMMENT '周总销售额（RMB）',
        weekSalesUsd DECIMAL(15,2) NULL COMMENT '周销售额（USD）',
        totalSalesUsd DECIMAL(15,2) NULL COMMENT '总销售额（USD）',
        totalSales DECIMAL(15,2) NULL COMMENT '总销售额（RMB）',
        relatedVideos INT NULL COMMENT '关联视频数',
        relatedAuthor INT NULL COMMENT '关联达人数',
        createTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='TikTok商品信息表'
    `;

    await connection.query(createTableSQL);
    console.log('t_product 表创建成功');

    // 创建索引
    await createIndexes(connection);

  } catch (error) {
    console.error('创建表失败:', error);
    throw error;
  }
}

/**
 * 创建索引
 */
async function createIndexes(connection) {
  try {
    console.log('创建索引...');

    const indexes = [
      'CREATE INDEX idx_product_name ON t_product (productName(255))',
      'CREATE INDEX idx_seller_name ON t_product (sellerName(255))',
      'CREATE INDEX idx_price_usd ON t_product (priceUsd)',
      'CREATE INDEX idx_price ON t_product (price)',
      'CREATE INDEX idx_week_sold_count ON t_product (weekSoldCount)',
      'CREATE INDEX idx_total_sales_usd ON t_product (totalSalesUsd)',
      'CREATE INDEX idx_rank ON t_product (`rank`)',
      'CREATE INDEX idx_create_time ON t_product (createTime)',
      'CREATE INDEX idx_update_time ON t_product (updateTime)'
    ];

    for (const indexSQL of indexes) {
      try {
        await connection.query(indexSQL);
      } catch (error) {
        if (error.code === 'ER_DUP_KEYNAME') {
          // 索引已存在，忽略错误
          continue;
        }
        throw error;
      }
    }

    console.log('索引创建完成');

  } catch (error) {
    console.error('创建索引失败:', error);
  }
}

/**
 * 插入示例数据
 */
async function insertSampleData(connection) {
  try {
    console.log('检查是否需要插入示例数据...');

    // 检查表是否为空
    const [rows] = await connection.query('SELECT COUNT(*) as count FROM t_product');
    const count = rows[0].count;

    if (count > 0) {
      console.log(`表中已有 ${count} 条数据，跳过示例数据插入`);
      return;
    }

    console.log('插入示例数据...');

    const sampleData = [
      [
        '1731808168774042656',
        'TikTok 热销商品示例 1',
        1,
        29.99,
        199.99,
        '示例店铺 A',
        1200,
        239988.00,
        35988.00,
        150000.00,
        999999.00,
        45,
        12
      ],
      [
        '1731808168774042657',
        'TikTok 热销商品示例 2',
        2,
        19.99,
        129.99,
        '示例店铺 B',
        800,
        103992.00,
        15992.00,
        80000.00,
        519996.00,
        28,
        8
      ]
    ];

    const insertSQL = `
      INSERT INTO t_product (
        id, productName, \`rank\`, priceUsd, price, sellerName,
        weekSoldCount, weekSales, weekSalesUsd, totalSalesUsd, totalSales,
        relatedVideos, relatedAuthor
      ) VALUES ?
    `;

    await connection.query(insertSQL, [sampleData]);
    console.log(`成功插入 ${sampleData.length} 条示例数据`);

  } catch (error) {
    console.error('插入示例数据失败:', error);
  }
}

// 运行初始化
if (require.main === module) {
  initializeDatabase();
}

module.exports = {
  initializeDatabase,
  createProductTable,
  insertSampleData
};